from django.shortcuts import render,redirect,HttpResponseRedirect
from .models import *
from django.db.models import Avg, Max, Min, Count, Sum
from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage
import datetime
import pymysql
import pandas as pd
from pyecharts.charts import Pie,Bar,Page
from pyecharts import options as opts
import matplotlib.pyplot as plt
from django.contrib import messages
from django.urls import reverse
from django.db.models import Q
import time

# Create your views here.

def hello(request):
    if request.method == "POST":
        #获取注册内容
        mobile = request.POST.get("mobile")  # 手机号
        department = request.POST.get("department") #部门
        the_name = request.POST.get("the_name")  #姓名
        password = request.POST.get("password")  #密码
        # 保存到数据库
        if mobile!='' and department !='' and the_name !='' and password !='':
            Business.objects.create(mobile=mobile,department=department ,the_name=the_name,password=password)
            return redirect("/index1/denglu") #重定向
    return render(request, 'zhuce.html')


def denglu_app(request):
    if request.method=='GET':
        denglu_mobile = request.GET.get("mobile", None)
        denglu_password = request.GET.get("password", None)
        user=Business.objects.filter(mobile=denglu_mobile,password=denglu_password) #条件查询
        print(denglu_mobile)
        if denglu_mobile!=None or denglu_password!=None:
            if user:
                return redirect("/index1/shop") #重定向
            else:
                messages.error(request, '用户名或密码不正确')
    return render(request, 'denglu.html')
#数据
def shop_goods(choice,fenlei,paixu):
    coon = pymysql.connect(host='localhost', user='root', password='123456', db='data-shop', port=3306,
                           charset='utf8')  # 创建数据库链接
    if choice=='否':
        if fenlei=='' or fenlei=='全部':
            df = pd.read_sql(
                'SELECT * from (select MIN(时间) ,款式编码 ,SUM(实发数量) from helloapp_sheet1 as b GROUP BY 款式编码) as g,helloapp_noallstype as a where a.`款式编码`=g.`款式编码`',
                con=coon)
        else:
            df = pd.read_sql(
                'SELECT * from (select MIN(时间) ,款式编码 ,SUM(实发数量) from helloapp_sheet1 as b GROUP BY 款式编码) as g,helloapp_noallstype as a where a.`款式编码`=g.`款式编码` and  a.`分类` like ("%{}%") '.format(fenlei),
                con=coon)
    else:
        if fenlei=='' or fenlei=='全部':
            df = pd.read_sql(
                'SELECT * from (select MIN(时间) ,款式编码 ,SUM(实发数量) from helloapp_sheet1 as b GROUP BY 款式编码) as g,helloapp_allstype as a where a.`款式编码`=g.`款式编码`',
                con=coon)
        else:
            df = pd.read_sql(
                'SELECT * from (select MIN(时间) ,款式编码 ,SUM(实发数量) from helloapp_sheet1 as b GROUP BY 款式编码) as g,helloapp_allstype as a where a.`款式编码`=g.`款式编码` and  a.`分类` like ("%{}%")'.format(fenlei),
                con=coon)
    if paixu=='上标':
        data=df.sort_values(by='MIN(时间)',axis=0, ascending=True)
        df = data.reset_index(drop=True)
    elif paixu=='下标':
        data=df.sort_values(by='MIN(时间)',axis=0, ascending=False)
        df = data.reset_index(drop=True)
    coon.close()
    datalist = []
    for i in range(df['库存'].count()):
        d = {
            'time': df['MIN(时间)'][i],
            'number': df['款式编码'].values[:,0][i],
            'cla':df['分类'][i],
            'sales': int(df['SUM(实发数量)'][i]),
            'tory': df['库存'][i]
        }
        datalist.append(d)
    return datalist

def anniu_goods(anniu,paixu):
    coon = pymysql.connect(host='localhost', user='root', password='123456', db='data-shop', port=3306,
                           charset='utf8')  # 创建数据库链接
    df1 = pd.read_sql(
        'SELECT * '
        'from (SELECT * from (select MIN(时间) ,款式编码 ,SUM(实发数量) from helloapp_sheet1 as b GROUP BY 款式编码) as g '
        'where g.`款式编码` like "%{}%") as ba,helloapp_allstype as bi  WHERE ba.`款式编码`=bi.`款式编码` '.format(anniu),
        con=coon)
    df2 = pd.read_sql(
        'SELECT * '
        'from (SELECT * from (select MIN(时间) ,款式编码 ,SUM(实发数量) from helloapp_sheet1 as b GROUP BY 款式编码) as g '
        'where g.`款式编码` like "%{}%") as ba,helloapp_noallstype as q  WHERE ba.`款式编码`=q.`款式编码`'.format(anniu),
        con=coon)
    df=pd.concat([df1,df2])
    df=df.reset_index(drop=True)
    if paixu=='上标':
        data=df.sort_values(by='MIN(时间)',axis=0, ascending=True)
        df = data.reset_index(drop=True)
    elif paixu=='下标':
        data=df.sort_values(by='MIN(时间)',axis=0, ascending=False)
        df = data.reset_index(drop=True)
    coon.close()
    datalist = []
    for i in range(df['库存'].count()):
        d = {
            'time': df['MIN(时间)'][i],
            'number': df['款式编码'].values[:,0][i],
            'cla': df['分类'][i],
            'sales': int(df['SUM(实发数量)'][i]),
            'tory': df['库存'][i]
        }
        datalist.append(d)
    return datalist
#传page
def index1(request):
    paixu = request .POST.get('paixu','')
    fenye=True
    choice = request.POST.get('choice', '')
    fenlei = request.POST.get('class', '')
    anniu = request.GET.get('anniu', '')
    if choice=='':
        choice = request.GET.get('choice', '')
    if fenlei=='':
        fenlei = request.GET.get('fenlei', '')
    if paixu=='':
        paixu = request.GET.get('paixu', '')
    if anniu=='':
        datalist = shop_goods(choice,fenlei,paixu)
    else:
        fenye = False
        datalist = anniu_goods(anniu,paixu)
    num_p = request.GET.get('page')  # 以page为键
    paginator = Paginator(datalist, 10)
    print(paginator)
    try:
        page_obj = paginator.get_page(num_p)
    except PageNotAnInteger:
        page_obj = paginator.page(1)
    if fenye:
        data = pagination_function(paginator, page_obj, is_paginated=True)
    else:
        pass
    return render(request, 'shop.html',locals())

# 分页
def pagination_function(paginator, page, is_paginated=True):
    if not is_paginated:
        # 如果没有分页，则无需显示分页导航条，不用任何分页导航条的数据，因此返回一个空的字典
        return {}

    # 当前页左边连续的页码号，初始值为空
    left = []

    # 当前页右边连续的页码号，初始值为空
    right = []

    # 标示第 1 页页码后是否需要显示省略号
    left_s = False

    # 标示最后一页页码前是否需要显示省略号
    right_s = False

    # 标示是否需要显示第 1 页的页码号。
    # 因为如果当前页左边的连续页码号中已经含有第 1 页的页码号，此时就无需再显示第 1 页的页码号，
    # 其它情况下第一页的页码是始终需要显示的。
    # 初始值为 False
    first = False

    # 标示是否需要显示最后一页的页码号。
    # 需要此指示变量的理由和上面相同。
    last = False

    # 获得用户当前请求的页码号
    page_number = page.number

    # 获得分页后的总页数
    total_pages = paginator.num_pages

    # 获得整个分页页码列表，比如分了四页，那么就是 [1, 2, 3, 4]
    page_range = paginator.page_range


    if page_number == 1:
        # 如果用户请求的是第一页的数据，那么当前页左边的不需要数据，因此 left=[]（已默认为空）。
        # 此时只要获取当前页右边的连续页码号，
        # 比如分页页码列表是 [1, 2, 3, 4]，那么获取的就是 right = [2, 3]。
        # 注意这里只获取了当前页码后连续两个页码，你可以更改这个数字以获取更多页码。
        right = page_range[page_number:page_number + 3]

        # 如果最右边的页码号比最后一页的页码号减去 1 还要小，
        # 说明最右边的页码号和最后一页的页码号之间还有其它页码，因此需要显示省略号，通过 right_has_more 来指示。
        if right[-1] < total_pages - 1:
            right_s = True

        # 如果最右边的页码号比最后一页的页码号小，说明当前页右边的连续页码号中不包含最后一页的页码
        # 所以需要显示最后一页的页码号，通过 last 来指示
        if right[-1] < total_pages:
            last = True

    elif page_number == total_pages:
        # 如果用户请求的是最后一页的数据，那么当前页右边就不需要数据，因此 right=[]（已默认为空），
        # 此时只要获取当前页左边的连续页码号。
        # 比如分页页码列表是 [1, 2, 3, 4]，那么获取的就是 left = [2, 3]
        # 这里只获取了当前页码后连续两个页码，你可以更改这个数字以获取更多页码。
        left = page_range[(page_number - 4) if (page_number - 4) > 0 else 0:page_number - 1]

        # 如果最左边的页码号比第 2 页页码号还大，
        # 说明最左边的页码号和第 1 页的页码号之间还有其它页码，因此需要显示省略号，通过 left_has_more 来指示。
        if left[0] > 3:
            left_s = True

        # 如果最左边的页码号比第 1 页的页码号大，说明当前页左边的连续页码号中不包含第一页的页码，
        # 所以需要显示第一页的页码号，通过 first 来指示
        if left[0] > 1:
            first = True
    else:
        # 用户请求的既不是最后一页，也不是第 1 页，则需要获取当前页左右两边的连续页码号，
        # 这里只获取了当前页码前后连续两个页码，你可以更改这个数字以获取更多页码。
        left = page_range[(page_number - 4) if (page_number - 4) > 0 else 0:page_number - 1]
        right = page_range[page_number:page_number + 3]

        # 是否需要显示最后一页和最后一页前的省略号
        if right[-1] < total_pages - 1:
            right_s = True
        if right[-1] < total_pages:
            last = True

        # 是否需要显示第 1 页和第 1 页后的省略号
        if left[0] > 2:
            left_s = True
        if left[0] > 1:
            first = True
    data = {
        'left': left,
        'right': right,
        'left_s': left_s,
        'right_s': right_s,
        'first': first,
        'last': last,
    }

    return data

#移出
def Remove_shop(request):
    number=request.GET.get('number')
    try :
        remove_number =  AllStype.objects.get(款式编码=number)
        change = NoallStype.objects.create(款式编码=remove_number.款式编码, 库存=remove_number.库存, 分类=remove_number.库存)
        print('所有表')
        AllStype.objects.get(款式编码=number).delete()
    except:
        remove_number = NoallStype.objects.get(款式编码=number)
        change = AllStype.objects.create(款式编码=remove_number.款式编码,库存=remove_number.库存,分类=remove_number.库存)
        print('部分表')
        NoallStype.objects.get(款式编码=number).delete()
    return redirect("/index1/shop") #重定向
#删除
def delete_shop(request):
    number = request.GET.get('number')
    try:
        delete_shop=NoallStype.objects.filter(款式编码=number).delete()
        delete_Sheet1 = Sheet1.objects.filter(款式编码=number).delete()
    except:
        delete_shop = AllStype.objects.filter(款式编码=number).delete()
        delete_Sheet1 = Sheet1.objects.filter(款式编码=number).delete()
    return redirect("/index1/shop")  # 重定向

#添加分类
def fenlei(request):
    all=AllStype.objects.values('款式编码')
    AllStype.objects.filter(款式编码__contains="Jan").delete()
    AllStype.objects.filter(款式编码__contains="Feb").delete()
    AllStype.objects.filter(款式编码__exact="nan").delete()
    for i in range(1914,AllStype.objects.count()):
        kk=Sheet1.objects.filter(款式编码=all[i].get('款式编码')).values('产品分类')[0]
        gg = AllStype.objects.get(款式编码=all[i].get('款式编码'))
        print(gg)
        print(i)
        gg.分类=kk.get('产品分类')
        gg.save()
    return render(request, 'shop.html')

def stock(choice, fenlei,paixu):
    coon = pymysql.connect(host='localhost', user='root', password='123456', db='data-shop', port=3306,
                           charset='utf8')  # 创建数据库链接
    if choice == '否':
        if fenlei == '' or fenlei == '全部':
            df = pd.read_sql(
                'SELECT * from (select 款式编码 ,SUM(实发数量) from helloapp_sheet1 as b  where TO_DAYS("2021-11-22") - TO_DAYS(时间) < 15 GROUP BY 款式编码) as g,helloapp_noallstype as a where a.`款式编码`=g.`款式编码`',
                con=coon)
        else:
            df = pd.read_sql(
                'SELECT * from (select 款式编码 ,SUM(实发数量) from helloapp_sheet1 as b where TO_DAYS("2021-11-22") - TO_DAYS(时间) < 15 GROUP BY 款式编码) as g,helloapp_noallstype as a where a.`款式编码`=g.`款式编码` and  a.`分类` like ("%{}%")'.format(fenlei),
                con=coon)
    else:
        if fenlei == '' or fenlei == '全部':
            df = pd.read_sql(
                'SELECT * from (select 款式编码 ,SUM(实发数量) from helloapp_sheet1 as b where TO_DAYS("2021-11-22") - TO_DAYS(时间) < 15 GROUP BY 款式编码) as g,helloapp_allstype as a where a.`款式编码`=g.`款式编码`',
                con=coon)
        else:
            df = pd.read_sql(
                'SELECT * from (select 款式编码 ,SUM(实发数量) from helloapp_sheet1 as b where TO_DAYS("2021-11-22") - TO_DAYS(时间) < 15 GROUP BY 款式编码) as g,helloapp_allstype as a where a.`款式编码`=g.`款式编码` and  a.`分类` like ("%{}%")' .format(fenlei),
                con=coon)
    if paixu=='上标':
        data=df.sort_values(by='库存',axis=0, ascending=True)
        df = data.reset_index(drop=True)
    elif paixu=='下标':
        data=df.sort_values(by='库存',axis=0, ascending=False)
        df = data.reset_index(drop=True)
    elif paixu=='上上标':
        data=df.sort_values(by='SUM(实发数量)',axis=0, ascending=True)
        df = data.reset_index(drop=True)
    elif paixu=='下下标':
        data=df.sort_values(by='SUM(实发数量)',axis=0, ascending=False)
        df = data.reset_index(drop=True)
    coon.close()
    datalist=[]
    for k in range(df['库存'].count()):
        if int(df['SUM(实发数量)'][k])==None:
            tory='无销量'
        else:
            tory=round(int(df['库存'][k])/(int(df['SUM(实发数量)'][k])/15),2)+2
        d={
            'number': df['款式编码'].values[:,0][k],
            'cation':df['分类'][k],
            'stock': df['库存'][k],
            'sales':int(df['SUM(实发数量)'][k]),
            'tory':tory,
            'production':df['生产周期'][k]
           }
        datalist.append(d)
    return datalist

def index2(request):
    paixu = request.POST.get('paixu', '')
    choice = request.POST.get('choice', '')
    fenlei = request.POST.get('class','')

    if choice == '':
        choice = request.GET.get('choice', '')
    if fenlei == '':
        fenlei = request.GET.get('fenlei', '')
    if paixu=='':
        paixu = request.GET.get('paixu', '')
    print(choice)
    print(paixu)
    print(fenlei)
    datalist = stock(choice, fenlei,paixu)
    paginator = Paginator(datalist, 10)
    num_p = request.GET.get('page')  # 以page为键
    try:
        page_obj = paginator.get_page(num_p)
    except PageNotAnInteger:
        page_obj = paginator.page(1)
    data = pagination_function(paginator, page_obj, is_paginated=True)
    return render(request, 'stock.html',locals())
#饼图
def get_total(data,a,b,g):
    if b==None:
        b='所有'
    if a==None:
        a='全部'
    all_total = []
    for i in data['SUM(实发数量)']:
        all_total.append(int(i))  # 由于列表里面元素的值为numpy.in64类型要将它转为int类型
    data_pair = [z for z in zip(data[g], all_total)]
    pie = (Pie(init_opts=opts.InitOpts(width="700px")  # 整个图像大小
               ).add("总数分析", data_pair=data_pair, radius="55%")  # 设置饼图内部的标签数值等等
        .set_global_opts(title_opts=opts.TitleOpts(title="{}{}占比  {}".format(a,g,b), pos_left=20, pos_top=50))  # 全局设置
        .set_series_opts(  # 标签设置
        tooltip_opts=opts.TooltipOpts(trigger="item", formatter="{a} <br/>{b}: {c} ({d}%)"),
        label_opts=opts.LabelOpts(formatter="{b}: {c} ({d}%)")
    ))

    return pie
#柱形图
def get_bar(data,g):
    all_bar=[]
    arry=[]
    for j,k in zip(data['SUM(实发数量)'],data[g]):
        all_bar.append(int(j))
        arry.append(k)
    bar=(Bar()
         .add_xaxis(arry) #X轴
         .add_yaxis('{}分布'.format(g),all_bar, bar_width="50%")
         .set_global_opts()
         )
    return bar

def color(request):
    coon = pymysql.connect(host='localhost', user='root', password='123456', db='data-shop', port=3306,
                           charset='utf8')  # 创建数据库链接
    a=request.POST.get("class")
    b=request.POST.get('shop-time')
    anniu = request.GET.get('anniu', '')
    if anniu == '':
        if b==None or b =='全部':
            if a=='全部' or a==None:
                df=pd.read_sql("select 颜色,SUM(实发数量) from helloapp_sheet1  GROUP BY 颜色",con=coon)
            else:
                sql1 = "select 颜色,SUM(实发数量) from helloapp_sheet1 where `产品分类` like ('%{}%') GROUP BY 颜色" .format(a)
                df=pd.read_sql(sql1,con=coon)
        elif b=='近一个月':
            if a=='全部' or a==None:
                df=pd.read_sql("select 颜色,SUM(实发数量) from helloapp_sheet1  where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 30 GROUP BY 颜色",con=coon)
            else:
                sql1 = "select 颜色,SUM(实发数量) from helloapp_sheet1 where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 30 and `产品分类` like ('%{}%') GROUP BY 颜色" .format(a)
                df=pd.read_sql(sql1,con=coon)
        elif b == '近三个月':
            if a=='全部' or a==None:
                df=pd.read_sql("select 颜色,SUM(实发数量) from helloapp_sheet1  where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 90 GROUP BY 颜色",con=coon)
            else:
                sql1 = "select 颜色,SUM(实发数量) from helloapp_sheet1 where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 90 and`产品分类` like ('%{}%') GROUP BY 颜色" .format(a)
                df=pd.read_sql(sql1,con=coon)
        elif b == '近六个月':
            if a=='全部' or a==None:
                df=pd.read_sql("select 颜色,SUM(实发数量) from helloapp_sheet1  where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 180 GROUP BY 颜色",con=coon)
            else:
                sql1 = "select 颜色,SUM(实发数量) from helloapp_sheet1 where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 180 and `产品分类` like ('%{}%') GROUP BY 颜色" .format(a)
                df=pd.read_sql(sql1,con=coon)
        coon.close()
        data=df.sort_values(by='SUM(实发数量)',axis=0, ascending=False)
        data = data.reset_index(drop=True)
        if int(data['颜色'].count())>10:
            data = data[:][:10].append(
                {'颜色': '其它颜色,共{}种'.format(int(data['颜色'].count()) - 10), 'SUM(实发数量)': data['SUM(实发数量)'][:][10:].sum()},
                ignore_index=True)
        pie=get_total(data,a,b,'颜色')
        bar=get_bar(data,'颜色')
        page = Page()
        page.add(pie,bar)
        page.render('E:\\1\\myweb\\helloapp\\templates\\shop-color.html')
    else:
        df = pd.read_sql("select 颜色,SUM(实发数量) from helloapp_sheet1  "
                         "where `款式编码`=('%s') GROUP BY 颜色" % (anniu), con=coon)
        coon.close()
        data = df.sort_values(by='SUM(实发数量)', axis=0, ascending=False)
        b = ''
        pie = get_total(data, anniu, b, '颜色')
        bar = get_bar(data, '颜色')
        page = Page()
        page.add(pie, bar)
        page.render('E:\\1\\myweb\\helloapp\\templates\\shop-color.html')
    return render(request, 'color.html')

def size(request):
    coon = pymysql.connect(host='localhost', user='root', password='123456', db='data-shop', port=3306,
                           charset='utf8')  # 创建数据库链接
    a=request.POST.get("class")
    b=request.POST.get('shop-time')
    anniu = request.GET.get('anniu', '')
    if anniu=='':
        if b == None or b == '全部':
            if a == '全部' or a == None:
                df = pd.read_sql("select 规格,SUM(实发数量) from helloapp_sheet1  GROUP BY 规格", con=coon)
            else:
                sql1 = "select 规格,SUM(实发数量) from helloapp_sheet1 where `产品分类` like ('%{}%') GROUP BY 规格" .format(a)
                df = pd.read_sql(sql1, con=coon)
        elif b == '近一个月':
            if a == '全部' or a == None:
                df = pd.read_sql(
                    "select 规格,SUM(实发数量) from helloapp_sheet1  where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 30 GROUP BY 规格",
                    con=coon)
            else:
                sql1 = "select 规格,SUM(实发数量) from helloapp_sheet1 where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 30 and `产品分类` like ('%{}%') GROUP BY 规格" .format(a)
                df = pd.read_sql(sql1, con=coon)
        elif b == '近三个月':
            if a == '全部' or a == None:
                df = pd.read_sql(
                    "select 规格,SUM(实发数量) from helloapp_sheet1  where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 90 GROUP BY 规格",
                    con=coon)
            else:
                sql1 = "select 规格,SUM(实发数量) from helloapp_sheet1 where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 90 and`产品分类` like ('%{}%') GROUP BY 规格" .format(a)
                df = pd.read_sql(sql1, con=coon)
        elif b == '近六个月':
            if a == '全部' or a == None:
                df = pd.read_sql(
                    "select 规格,SUM(实发数量) from helloapp_sheet1  where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 180 GROUP BY 规格",
                    con=coon)
            else:
                sql1 = "select 规格,SUM(实发数量) from helloapp_sheet1 where TO_DAYS('2021-11-22') - TO_DAYS(时间) < 180 and `产品分类` like ('%{}%') GROUP BY 规格".format(a)
                df = pd.read_sql(sql1, con=coon)
        coon.close()
        data = df.sort_values(by='SUM(实发数量)', axis=0, ascending=False)
        pie = get_total(data, a, b, '规格')
        bar = get_bar(data, '规格')
        page = Page()
        page.add(pie, bar)
        page.render('E:\\1\\myweb\\helloapp\\templates\\shop-color.html')
    else:
        df=pd.read_sql("select 规格,SUM(实发数量) from helloapp_sheet1  "
                       "where `款式编码`=('%s') GROUP BY 规格" %(anniu),con=coon)
        coon.close()
        data = df.sort_values(by='SUM(实发数量)', axis=0, ascending=False)
        b=''
        pie=get_total(data,anniu,b,'规格')
        bar=get_bar(data,'规格')
        page = Page()
        page.add(pie,bar)
        page.render('E:\\1\\myweb\\helloapp\\templates\\shop-color.html')
    return render(request, 'color.html')

def sales(choice, fenlei):
    coon = pymysql.connect(host='localhost', user='root', password='123456', db='data-shop', port=3306,
                           charset='utf8')  # 创建数据库链接
    if choice == '否':
        if fenlei == '' or fenlei == '全部':
            df = pd.read_sql(
                'select * '
                'FROM(select * from (SELECT `款式编码` as number,SUM(`实发数量`) as 上周销量 from helloapp_sheet1  where TO_DAYS("2021-11-22") - TO_DAYS(时间) <7 GROUP BY `款式编码`) as g,helloapp_noallstype as a where g.number=a.`款式编码`) as k '
                'LEFT JOIN (SELECT 款式编码 as numbes,SUM(`实发数量`) as 上上周销量  from helloapp_sheet1  where TO_DAYS("2021-11-22") - TO_DAYS(时间) <14 and `时间`not in(SELECT `时间` from helloapp_sheet1 where TO_DAYS("2021-11-22") - TO_DAYS(时间) <7) GROUP BY numbes) as table1 '
                'on k.number=table1.numbes',
                con=coon)
        else:
            df = pd.read_sql(
                'select * '
                'FROM(select * from (SELECT `款式编码` as number,SUM(`实发数量`) as 上周销量 from helloapp_sheet1  where TO_DAYS("2021-11-22") - TO_DAYS(时间) <7 GROUP BY `款式编码`) as g,helloapp_noallstype as a where g.number=a.`款式编码` and  a.`分类` like ("%{}%")) as k '
                'LEFT JOIN (SELECT 款式编码 as numbes,SUM(`实发数量`) as 上上周销量  from helloapp_sheet1  where TO_DAYS("2021-11-22") - TO_DAYS(时间) <14 and `时间`not in(SELECT `时间` from helloapp_sheet1 where TO_DAYS("2021-11-22") - TO_DAYS(时间) <7) GROUP BY numbes) as table1 '
                'on k.number=table1.numbes'.format(fenlei),
                con=coon)
    else:
        if fenlei == '' or fenlei == '全部':
            df = pd.read_sql(
                'select * '
                'FROM(select * from (SELECT `款式编码` as number,SUM(`实发数量`) as 上周销量 from helloapp_sheet1  where TO_DAYS("2021-11-22") - TO_DAYS(时间) <7 GROUP BY `款式编码`) as g,helloapp_allstype as a where g.number=a.`款式编码`) as k '
                'LEFT JOIN (SELECT 款式编码 as numbes,SUM(`实发数量`) as 上上周销量  from helloapp_sheet1  where TO_DAYS("2021-11-22") - TO_DAYS(时间) <14 and `时间`not in(SELECT `时间` from helloapp_sheet1 where TO_DAYS("2021-11-22") - TO_DAYS(时间) <7) GROUP BY numbes) as table1 '
                'on k.number=table1.numbes',
                con=coon)
        else:
            df = pd.read_sql(
                'select * '
                'FROM(select * from (SELECT `款式编码` as number,SUM(`实发数量`) as 上周销量 from helloapp_sheet1  where TO_DAYS("2021-11-22") - TO_DAYS(时间) <7 GROUP BY `款式编码`) as g,helloapp_allstype as a where g.number=a.`款式编码` and  a.`分类` like ("%{}%")) as k '
                'LEFT JOIN (SELECT 款式编码 as numbes,SUM(`实发数量`) as 上上周销量  from helloapp_sheet1  where TO_DAYS("2021-11-22") - TO_DAYS(时间) <14 and `时间`not in(SELECT `时间` from helloapp_sheet1 where TO_DAYS("2021-11-22") - TO_DAYS(时间) <7) GROUP BY numbes) as table1 '
                'on k.number=table1.numbes'.format(fenlei),
                con=coon)
    coon.close()
    data = df.sort_values(by='上周销量', axis=0, ascending=False)
    df = data.reset_index(drop=True) #重新建立索引
    df['上上周销量'].fillna(value=0,inplace=True)
    datalist=[]
    for k in range(df['库存'].count()):
        ever = int(df['库存'][k]) / ((df['上周销量'][k]+df['上上周销量'][k]) / 14) + 2
        sales_1=int(df['上周销量'][k])
        sales_2 = int(df['上上周销量'][k])
        if ever >= int(df['生产周期'][k]) :
            warn='否'
        else:
            warn = '是'
        d={
            'rank':k+1,
            'number': df['款式编码'][k],
            'cation':df['分类'][k],
            'sales_1': sales_1,
            'sales_2':sales_2,
            'stock': df['库存'][k],
            'warn':warn,
           }
        datalist.append(d)
    return datalist

def index3(request):
    time_stamp = str(round(time.time() * 1000))
    message1= "/static/images/num.png?res=" + time_stamp
    choice = request.POST.get('choice', '')
    fenlei = request.POST.get('class', '')
    if choice == '':
        choice = request.GET.get('choice', '')
    if fenlei == '':
        fenlei = request.GET.get('fenlei', '')
    print(fenlei)
    datalist = sales(choice, fenlei)
    paginator = Paginator(datalist, 10)
    num_p = request.GET.get('page')  # 以page为键
    print(num_p)
    try:
        page_obj = paginator.get_page(num_p)
    except PageNotAnInteger:
        page_obj = paginator.page(1)
    data = pagination_function(paginator, page_obj, is_paginated=True)
    return render(request, 'sales.html',locals())

def new_shop(request):
    coon = pymysql.connect(host='localhost', user='root', password='123456', db='data-shop', port=3306,
                           charset='utf8')  # 创建数据库链接
    df = pd.read_sql(
        'select m.`款式编码`,SUM(`实发数量`),`时间` '
        'from helloapp_sheet1 as m,'
        '(select * from (select `款式编码`,MIN(`时间`) as time from helloapp_sheet1 GROUP BY `款式编码`) as n '
        'where Date(n.time)  between DATE_ADD("2021-11-22", INTERVAL -7 DAY) and "2021-11-22") as j '
        'where m.`款式编码` =j.`款式编码` '
        'GROUP BY m.`款式编码`,`时间`',
        con=coon)
    number = df['款式编码'].drop_duplicates().to_list()
    end = datetime.date(2021,11,22)
    again = end - datetime.timedelta(days=6)
    # k= datetime.datetime.strptime('2021-11-17','%Y-%m-%d').date()
    datalist=[]
    oog=[]
    date=[]
    for o in range((end - again).days+1):
        day = again + datetime.timedelta(days=o)
        date.append(day)
    for j in number:
        for i in range((end - again).days +1):
            day = again + datetime.timedelta(days=i)
            try:
                g=df[(df['款式编码'] == j) & (df['时间'] == day)]['SUM(`实发数量`)'].iloc[0]
            except:
                g=0
            d={
                'sales_1':int(g),
            }
            oog.append(d)
        k={
            'number': j,
            'sales':oog
        }
        datalist.append(k)
        oog=[]
    kkpc={
        'datt':datalist}
    nnm=[]
    nnm.append(kkpc)
    # for gg in datalist:
    #     print(gg.sales)
    return render(request,'new_shop.html',{'datalist':datalist,'date':date})

def draw(request):
    number = request.GET.get('number')
    choice = request.POST.get('choice', '')
    fenlei = request.POST.get('class', '')
    if choice == '':
        choice = request.GET.get('choice', '')
    if fenlei == '':
        fenlei = request.GET.get('fenlei', '')
    print(choice)
    print(fenlei)
    num_p = request.GET.get('page')  # 以page为键
    print(num_p)
    coon = pymysql.connect(host='localhost', user='root', password='123456', db='data-shop', port=3306,
                           charset='utf8')  # 创建数据库链接
    df = pd.read_sql('select 时间,SUM(`实发数量`) from helloapp_sheet1 where `款式编码`=("%s") and TO_DAYS("2021-11-22") - TO_DAYS(时间) < 15 GROUP BY 时间'
        %(number),con=coon)
    plt.figure(figsize=(12,5),dpi=100)
    plt.rcParams['lines.linewidth'] = 3
    plt.rcParams['font.sans-serif']='SimHei'
    for i in range(df['时间'].count()):
        plt.text(df['时间'][i], df['SUM(`实发数量`)'][i], df['SUM(`实发数量`)'][i], ha='center',va='center',fontsize=10)
    plt.plot(df['时间'], df['SUM(`实发数量`)'], color='orange', linestyle='--', marker='o')
    plt.xlabel('日期');plt.ylabel('销售数量（件）')
    plt.title('{}近{}天销量折线图'.format(number,df['时间'].count()))
    plt.savefig('E:\\1\\myweb\\helloapp\\statics\\images\\num.png')
    return redirect("/index1/sales?page={}&choice={}&fenlei={}".format(num_p,choice,fenlei))

def yonghu(request):
    yonghu_list=Business.objects.all()
    return render(request,'yonghu.html',{'yonghu_list':yonghu_list})